package app.TianQi;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Random;

import qiurank.Common;
import qiurank.DB;
import qiurank.Log;
import qiurank.UrlObject;

/**
 * Created by Administrator on 2016/1/14.
 */
public class WeatherModel extends Object{
    private DB.Table weatherdb;
    private DB.Table areadb;
    private DB.Table citydb;
    public WeatherModel(){
        whetherRenameTable();
        weatherdb=DB.table("weather");
        areadb=DB.table("area");
        citydb=DB.table("city");
    }

    public List<UrlObject> getWeatherIdList(int skip,int take){
        List<UrlObject> list=new ArrayList<>();
        try{
            citydb.select("DISTINCT weatherID,city").whereNotNull("weatherID").skip(skip).take(take).orderBy("weatherID","desc");
            ResultSet rs=areadb.select("DISTINCT weatherID,area").whereNotNull("weatherID").union(citydb).get();
            while (rs.next()){
                UrlObject ul=new UrlObject();
                ul.url="http://www.weather.com.cn/weather1d/"+rs.getString("weatherID")+".shtml";
                ul.key=1;
                ul.params=rs.getString("weatherID");
                list.add(ul);
            }
            rs.close();
            return list;
        }catch (Exception e){

        }
        return null;

    }
    public void saveNRelWeather(Map<String,Object>map){
        String weatherID=map.get("weatherID").toString();
        if(!weatherID.isEmpty()){
            if(isExistWeather(weatherID,0)){
                ResultSet rs=getWeatherByWeatherID(weatherID,0);
                if(rs!=null){
                    try {
                        int id=rs.getInt("id");
                        if(id>0){
                            map.put("edit_time2", Common.getNowDate());
                            // Log.console("update:" + map.get("cityname")+",id:"+id);
                            weatherdb.where("id",id).update(map);
                        }
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
            }
        }
    }
    //保存明天的天气
    public void saveTomorrowWeather(Map<String,Object>map){
        String weatherID=map.get("weatherID").toString();

        if(!weatherID.isEmpty()){
            if(!isExistWeather(weatherID,Common.getAnyDateNingChen(1))){
                try {
                    weatherdb.insert(map);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    //保存天气实时部分
    public void saveRelWeather(Map<String,Object>map){
        String weatherID=map.get("weatherID").toString();
        if(!weatherID.isEmpty()){
            if(isExistWeather(weatherID,0)){
                ResultSet rs=getWeatherByWeatherID(weatherID,0);
                if(rs!=null){
                    try {
                        int id=rs.getInt("id");
                        if(id>0){
                            map.put("edit_time", Common.getNowDate());
                           // Log.console("update:" + map.get("cityname")+",id:"+id);
                            weatherdb.where("id",id).update(map);
                        }
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
            }else{
                map.put("create_time",Common.getNowDateNingChen()/1000);
                map.put("edit_time",Common.getNowDate());
                try {
                    //Log.console("insert:"+map.get("cityname"));
                    weatherdb.insert(map);

                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public ResultSet getWeatherByWeatherID(String weather_id,long date_time){
        if(date_time==0){
           date_time=Common.getNowDateNingChen();
        }
        whetherChangeTable(date_time);
        if(!weather_id.isEmpty()){
            try {
                ResultSet rs= weatherdb.where("weatherID",weather_id).where("create_time",date_time/1000).take(1).get();
                if(rs!=null&&rs.next()){
                    return rs;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 根据时间戳判断是否要切换表
     * @param date_time  java时间戳
     */
    private void whetherChangeTable(long date_time){
        Calendar t=Calendar.getInstance();
        Date passDate=new Date(date_time);
        t.setTime(passDate);
        int passYear=t.get(Calendar.YEAR);
        int passMonth=t.get(Calendar.MONTH)+1;
        t.setTime(new Date());
        int nowYear=t.get(Calendar.YEAR);
        int nowMonth=t.get(Calendar.MONTH)+1;
        int d1=passYear*12+passMonth;
        int d2=nowMonth+nowYear*12;
        String mon="";
        if(d2-d1>=4){
            if(passMonth<10){
                mon="0"+passMonth;
            }else {
                mon=""+passMonth;
            }
            try {
                ResultSet rs = DB.createDB().query("SHOW TABLES LIKE '" + weatherdb.getTableFix() + "_weather" + "_" + passYear+"_"+mon+"'");
                if(rs.next()){
                    weatherdb = DB.table("weather" + "_" + passYear+"_"+mon);
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }


        }
    }
    private void whetherRenameTable(){
        String premoth=Common.getNowDateToPreMonth(-4);
        try {
            ResultSet rs=DB.createDB().query("SELECT count(*) as isexist FROM information_schema.TABLES WHERE table_name = 'jp_weather_" + premoth + "' AND TABLE_SCHEMA = '" + DB.DBName() + "'");
            if(rs!=null&&rs.next()){
               if(rs.getInt("isexist")==0){
                   DB.createDB().execute("RENAME table jp_weather to jp_weather_"+premoth);
                   String newtable_sql="CREATE TABLE `jp_weather` (" +
                           "                `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识id'," +
                           "                `nameen` varchar(50) DEFAULT NULL COMMENT '拼音'," +
                           "                `cityname` varchar(50) DEFAULT NULL COMMENT '城市名称'," +
                           "                `city_id` int(11) DEFAULT '0' COMMENT '城市id'," +
                           "                `weatherID` varchar(10) DEFAULT NULL COMMENT '天气网的城市地区编码'," +
                           "                `area_id` int(11) DEFAULT '0' COMMENT '地区id'," +
                           "                `temp` decimal(11,2) DEFAULT '0.00' COMMENT '摄氏实时温度'," +
                           "                `tempf` decimal(11,2) DEFAULT NULL COMMENT '实时华氏温度'," +
                           "                `wd` varchar(50) DEFAULT NULL COMMENT '实时风向'," +
                           "                `wde` varchar(10) DEFAULT NULL COMMENT '实时风向英文缩写'," +
                           "                `ws` varchar(50) DEFAULT NULL COMMENT '实时风力'," +
                           "                `wse` varchar(50) DEFAULT NULL COMMENT '实时风速'," +
                           "                `sd` int(11) DEFAULT NULL COMMENT '实时湿度百分比'," +
                           "                `rtime` int(11) DEFAULT '0' COMMENT '实时更新的时间'," +
                           "                `weather` varchar(20) DEFAULT NULL COMMENT '实时天气(晴,阴,雨)等'," +
                           "                `aqiname` varchar(10) DEFAULT NULL COMMENT '实时空气质量级别'," +
                           "                `aqi` int(11) DEFAULT '0' COMMENT '实时空气质量'," +
                           "                `day_temp` int(11) DEFAULT '0' COMMENT '白天温度'," +
                           "                `day_wea` varchar(50) DEFAULT NULL COMMENT '白天天气情况'," +
                           "                `day_wd` varchar(50) DEFAULT NULL COMMENT '白天风向'," +
                           "                `day_ws` varchar(50) DEFAULT NULL COMMENT '白天风力'," +
                           "                `sun_up` int(11) DEFAULT '0' COMMENT '日出时间'," +
                           "                `night_temp` int(11) DEFAULT '0' COMMENT '晚上温度'," +
                           "                `night_wea` varchar(50) DEFAULT NULL COMMENT '夜间天气情况'," +
                           "                `night_wd` varchar(50) DEFAULT NULL COMMENT '晚上风向'," +
                           "                `night_ws` varchar(50) DEFAULT NULL COMMENT '晚上风力'," +
                           "                `sun_down` int(11) DEFAULT '0' COMMENT '日落时间'," +
                           "                `create_time` int(11) DEFAULT '0' COMMENT '日期'," +
                           "                `edit_time` datetime DEFAULT NULL COMMENT '更新时间'," +
                           "                `edit_time2` datetime DEFAULT NULL COMMENT '非实时部分的更新时间'," +
                           "                PRIMARY KEY (`id`)," +
                           "                KEY `city_id` (`city_id`) USING BTREE," +
                           "                KEY `weatherID` (`weatherID`,`create_time`) USING BTREE" +
                           "              ) ENGINE=MyISAM AUTO_INCREMENT=86578 DEFAULT CHARSET=utf8";
                   DB.createDB().execute(newtable_sql);
               }
                rs.close();
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 判断天气id在某一天是否存在
     * @param weather_id  天气id
     * @param today   时间 java的时间戳，不用除以1000
     * @return  存在返回true 否则返回false
     */
    public boolean isExistWeather(String weather_id,long today){
        Calendar t=Calendar.getInstance();
        if(today==0){
            today= Common.getNowDateNingChen();
        }
        whetherChangeTable(today);
        try {
            ResultSet rs=weatherdb.select("count(*) as c").where("weatherID",weather_id).where("create_time",today/1000).take(1).get();
            if(rs!=null&&rs.next()){
                if(rs.getInt("c")>0){
                    rs.close();
                    return  true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


}
